package com.gjj.abox.database;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.gjj.abox.entity.Account;
import com.gjj.abox.entity.User;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@SuppressLint("SimpleDateFormat")
public class BoxDB extends SQLiteOpenHelper {

	public static final String DB_NAME = "AccountBox.db";

	private static final String CREATE_TBL1 = "create table user(_id integer primary key,"
			+ "userName text,userPwd text,userQuestion,userKey text)";

	public BoxDB(Context c) {

		super(c, DB_NAME, null, 1);
	}

	// 创建表
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TBL1);
		db.execSQL("CREATE TABLE paltfor (id integer primary key autoincrement, platformName varchar(18), userName varchar(18),pSetTime datetime)");
		db.execSQL("CREATE TABLE account (id integer primary key autoincrement, userName varchar(18), platformName varchar(18),accountName varchar(24),accountPwd varchar(18),accountNickName varchar(30),accountTel varchar(13),accountEmail varchar(24),accountQuestion varchar(80),accountMark varchar(50),aSetTime datetime)");
	}

	// 注册软件用户
	public void insertRegister(String name, String password, String question,
			String key) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "insert into user values(null,?,?,?,?)";
		db.execSQL(sql, new Object[] { name, password, question, key });
		db.close();
	}

	//查询用户是否已存在
	public boolean checkUserAccountIsExist(String name){
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select * from user where username=?";
		Cursor cur = db.rawQuery(sql, new String[] { name });
		if(cur.getCount()>0){
			cur.close();
			db.close();
			return true;
		}
		return false;
	}

	// 添加平台
	public void insertPaltfor(String pname, String uname) {
		SQLiteDatabase db = this.getWritableDatabase();
		Date date = new Date();
		String sql = "insert into paltfor values(null,?,?,?)";
		db.execSQL(sql, new Object[] { pname, uname,
				new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date) });
		db.close();
	}

	// 添加账户
	public void insertAccount(String userName, String platformName,
			String accountName, String accountPwd, String accountNickName,
			String accountTel, String accountEmail, String accountQuestion,
			String accountMark) {
		SQLiteDatabase db = this.getWritableDatabase();
		Date date = new Date();
		String sql = "insert into account values(null,?,?,?,?,?,?,?,?,?,?)";
		db.execSQL(sql, new Object[] { userName, platformName, accountName,
				accountPwd, accountNickName, accountTel, accountEmail,
				accountQuestion, accountMark,
				new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date) });
		db.close();
	}

	// 查询平台名
	public List<Account> queryPName(String userName, int i) {
		SQLiteDatabase db = getWritableDatabase();
		List<Account> list = null;
		String sql = "select distinct * from paltfor where username=? order by pSetTime desc limit ?,15";
		Cursor cur = db.rawQuery(sql,
				new String[] { userName, String.valueOf(i) });
		if (cur.getCount() > 0) {
			list = new ArrayList();
			cur.moveToFirst();
			while (!cur.isAfterLast()) {
				Account account = new Account();
				account.setPlatformName(cur.getString(1));
				account.setSetTime(cur.getString(3));
				list.add(account);
				cur.moveToNext();
			}
		} else {
			System.out.println("cur is null");
		}
		cur.close();
		db.close();
		return list;
	}

	// 判断帐户名是否已经存在
	public boolean checkAccountNull(String paltfor, String username,
			String accountname) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select * from account where platformname=? and username=? and accountname=?";
		Cursor cur = db.rawQuery(sql, new String[] { paltfor, username,
				accountname });
		if (cur.getCount() > 0) {
			cur.close();
			db.close();
			return true;
		}
		cur.close();
		db.close();
		return false;
	}

	// 查询平台名是否重复
	public List<String> queryPName_cf(String platformName) {
		SQLiteDatabase db = getWritableDatabase();
		List<String> list = null;
		String sql = "select userName from paltfor where platformName=?";
		Cursor cur = db.rawQuery(sql, new String[] { platformName });
		if (cur.getCount() > 0) {
			list = new ArrayList<String>();
			cur.moveToFirst();
			while (!cur.isAfterLast()) {
				list.add(cur.getString(0));
				cur.moveToNext();
			}
		}
		cur.close();
		db.close();
		return list;
	}

	// 登录查询
	public String queryPwdByName(String name) {
		String password = null;
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select userpwd from user where username=?";
		Cursor cur = db.rawQuery(sql, new String[] { name });
		if (cur.getCount() > 0) {
			cur.moveToFirst();
			password = cur.getString(0);
		} else {
			System.out.println("cur is null");
		}
		cur.close();
		db.close();
		return password;
	}

	// 查询帐户名列表
	public List<String> queryAccountName(String username, String platformname) {
		List<String> list = null;
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select accountName from account where username=? and platformname=? order by aSetTime desc";
		Cursor cur = db.rawQuery(sql, new String[] { username, platformname });
		if (cur.getCount() > 0) {
			list = new ArrayList<String>();
			cur.moveToFirst();
			while (!cur.isAfterLast()) {
				list.add(cur.getString(0));
				cur.moveToNext();
			}
		} else {
			System.out.println("cur is null");
		}
		cur.close();
		db.close();
		return list;

	}

	// 详细页面查询
	public Account queryAccountAll(String username, String platformname,
								   String accountname) {
		Account account = null;
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select * from account where username=? and platformname=? and accountname=?";
		Cursor cur = db.rawQuery(sql, new String[] { username, platformname,
				accountname });
		if (cur.getCount() > 0) {
			account = new Account();
			cur.moveToFirst();
			account.setAccountId(cur.getInt(0));
			account.setUserName(cur.getString(1));
			account.setPlatformName(cur.getString(2));
			account.setAccountName(cur.getString(3));
			account.setAccountPwd(cur.getString(4));
			account.setAccountNickName(cur.getString(5));
			account.setAccountTel(cur.getString(6));
			account.setAccountEmail(cur.getString(7));
			account.setAccountQuestion(cur.getString(8));
			account.setAccountMark(cur.getString(9));
			account.setSetTime(cur.getString(10));
		} else {
			System.out.println("cur is null");
		}
		cur.close();
		db.close();
		return account;
	}

	// 找回密码查询
	public User queryUserAll(String name) {
		User user = new User();
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select * from user where userName=?";
		Cursor cur = db.rawQuery(sql, new String[] { name });
		if (cur.getCount() > 0) {
			cur.moveToFirst();
			user.setUserId(cur.getInt(0));
			user.setUserName(cur.getString(1));
			user.setUserPwd(cur.getString(2));
			user.setUserQuestion(cur.getString(3));
			user.setUserKey(cur.getString(4));
		}
		cur.close();
		db.close();
		return user;
	}

	// 判断该平台里面记录是否为空
	public boolean checkNull(String paltfor, String username) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select * from account where platformname=? and username=?";
		Cursor cur = db.rawQuery(sql, new String[] { paltfor, username });
		if (cur.getCount() > 0) {
			cur.close();
			db.close();
			return true;
		}
		cur.close();
		db.close();
		return false;
	}

	// 删除平台
	public void deletePalt(String paltfor, String username) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "delete from paltfor where platformname=? and username=?";
		db.execSQL(sql, new String[] { paltfor, username });
		db.close();
	}

	// 删除账户
	public void deleteAccount(String username, String platformname,
			String accountname) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "DELETE from account where username=? and platformname=? and accountname=?";
		db.execSQL(sql, new String[] { username, platformname, accountname });
		db.close();
	}

	// 编辑更新数据
	public void updataAccount(String accountId,String accountNickName, String accountPwd,
			String accountTel, String accountEmail, String accountQuestion,
			String accountMark, String userName, String platformName,
			String accountName) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "update account set accountName=?,accountNickName=?,accountPwd=?,accountTel=?,"
				+ "accountEmail=?,accountQuestion=?,accountMark=?"
				+ "   where username=? and platformName=? and accountName=? ";
		db.execSQL(sql, new String[] {accountId, accountNickName, accountPwd, accountTel,
				accountEmail, accountQuestion, accountMark, userName,
				platformName, accountName });
		db.close();
	}

	// 修改paltfor表里面的平台名
	public void updatePlatformName1(String plat, String userName,
			String platformName) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "update paltfor set platformName=? where username=? and platformName=?";
		db.execSQL(sql, new String[] { plat, userName, platformName });
		db.close();
	}

	// 修改account表里面的平台名
	public void updatePlatforName2(String plat, String userName,
			String platformName) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "update account set platformName=? where username=? and platformName=?";
		db.execSQL(sql, new String[] { plat, userName, platformName });
		db.close();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

}
